from abc import ABC, abstractmethod
from typing import Dict, List, Optional, Any, Tuple
from app.models.db import get_db_connection, execute_query
from app.utils.log_config import get_logger
from app.utils.pagination import Pagination

logger = get_logger('model')

class Model(ABC):
    """
    数据模型基类
    提供通用的数据库操作方法
    """
    
    # 表名
    table_name = ""
    
    # 主键字段名
    primary_key = "id"
    
    def __init__(self, **kwargs):
        """
        初始化模型实例
        """
        for key, value in kwargs.items():
            setattr(self, key, value)
    
    @classmethod
    def find_by_id(cls, id_value: Any) -> Optional['Model']:
        """
        根据ID查找记录
        """
        try:
            with get_db_connection() as conn:
                query = f"SELECT * FROM {cls.table_name} WHERE {cls.primary_key} = %s"
                results = execute_query(conn, query, (id_value,))
                
                if not results:
                    return None
                
                # 创建模型实例
                return cls._create_instance(results[0])
        except Exception as e:
            logger.error(f"查询{cls.table_name}失败: {e}")
            raise
    
    @classmethod
    def find_all(cls, **kwargs) -> List['Model']:
        """
        查找所有记录，可选择性地添加过滤条件
        """
        try:
            with get_db_connection() as conn:
                query = f"SELECT * FROM {cls.table_name}"
                params = []
                
                # 添加过滤条件
                if kwargs:
                    conditions = []
                    for key, value in kwargs.items():
                        conditions.append(f"{key} = %s")
                        params.append(value)
                    
                    if conditions:
                        query += " WHERE " + " AND ".join(conditions)
                
                results = execute_query(conn, query, params)
                
                # 创建模型实例列表
                return [cls._create_instance(row) for row in results]
        except Exception as e:
            logger.error(f"查询{cls.table_name}失败: {e}")
            raise
    
    @classmethod
    def paginate(cls, page: int = 1, per_page: int = 20, 
                order_by: Optional[str] = None, **filters) -> Pagination['Model']:
        """
        分页查询
        
        Args:
            page: 页码
            per_page: 每页数量
            order_by: 排序字段
            **filters: 过滤条件
            
        Returns:
            分页对象
        """
        try:
            with get_db_connection() as conn:
                # 构建基础查询
                base_query = f"SELECT * FROM {cls.table_name}"
                count_query = f"SELECT COUNT(*) FROM {cls.table_name}"
                params = []
                
                # 添加过滤条件
                if filters:
                    conditions = []
                    for key, value in filters.items():
                        conditions.append(f"{key} = %s")
                        params.append(value)
                    where_clause = " WHERE " + " AND ".join(conditions)
                    base_query += where_clause
                    count_query += where_clause
                
                # 添加排序
                if order_by:
                    base_query += f" ORDER BY {order_by}"
                
                # 添加分页
                offset = (page - 1) * per_page
                base_query += f" LIMIT %s OFFSET %s"
                pagination_params = params + [per_page, offset]
                
                # 获取总数
                cursor = conn.cursor()
                cursor.execute(count_query, params)
                total = cursor.fetchone()[0]
                
                # 获取当前页数据
                results = execute_query(conn, base_query, pagination_params)
                
                # 转换为模型实例
                items = [cls._create_instance(row) for row in results]
                
                # 创建分页对象
                return Pagination(
                    items=items,
                    page=page,
                    per_page=per_page,
                    total=total
                )
                
        except Exception as e:
            logger.error(f"分页查询{cls.table_name}失败: {e}")
            raise
    
    @classmethod
    def raw_query(cls, query: str, params: Tuple = (), 
                 as_dict: bool = False) -> List[Any]:
        """
        执行原生SQL查询
        
        Args:
            query: SQL查询语句
            params: 查询参数
            as_dict: 是否返回字典格式
            
        Returns:
            查询结果列表
        """
        try:
            with get_db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(query, params)
                results = cursor.fetchall()
                return results
        except Exception as e:
            logger.error(f"原生查询失败: {e}")
            raise
    
    @classmethod
    def execute(cls, query: str, params: Tuple = ()) -> int:
        """
        执行SQL语句（如INSERT, UPDATE, DELETE）
        
        Args:
            query: SQL语句
            params: 参数
            
        Returns:
            影响的行数
        """
        try:
            with get_db_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(query, params)
                conn.commit()
                return cursor.rowcount
        except Exception as e:
            logger.error(f"执行SQL失败: {e}")
            raise
    
    @classmethod
    def _create_instance(cls, row: Any) -> 'Model':
        """
        从数据库行创建模型实例
        """
        # 处理不同数据库返回格式
        if hasattr(row, 'keys'):
            # 命名元组或字典
            return cls(**dict(row))
        else:
            # 普通元组，需要子类实现映射
            return cls._tuple_to_instance(row)
    
    @classmethod
    @abstractmethod
    def _tuple_to_instance(cls, row: tuple) -> 'Model':
        """
        将元组转换为模型实例（子类必须实现）
        """
        pass
    
    def to_dict(self) -> Dict[str, Any]:
        """
        将模型实例转换为字典
        """
        # 获取所有实例属性（不包括方法和内置属性）
        return {k: v for k, v in self.__dict__.items() if not k.startswith('_')}
    
    def save(self) -> bool:
        """
        保存模型实例（插入或更新）
        """
        if hasattr(self, self.primary_key) and getattr(self, self.primary_key) is not None:
            return self._update()
        else:
            return self._insert()
    
    def _insert(self) -> bool:
        """
        插入新记录
        """
        try:
            with get_db_connection() as conn:
                # 获取所有字段和值
                data = self.to_dict()
                
                # 移除主键（如果存在且为None）
                if self.primary_key in data and data[self.primary_key] is None:
                    del data[self.primary_key]
                
                # 构建插入语句
                fields = ', '.join(data.keys())
                placeholders = ', '.join(['%s'] * len(data))
                query = f"INSERT INTO {self.table_name} ({fields}) VALUES ({placeholders})"
                
                # 执行插入
                cursor = conn.cursor()
                cursor.execute(query, list(data.values()))
                
                # 获取插入的ID
                if self.primary_key and self.primary_key not in data:
                    # SQLite
                    if hasattr(cursor, 'lastrowid'):
                        setattr(self, self.primary_key, cursor.lastrowid)
                    # PostgreSQL
                    elif hasattr(conn, 'cursor'):
                        cursor.execute("SELECT lastval()")
                        setattr(self, self.primary_key, cursor.fetchone()[0])
                
                conn.commit()
                logger.info(f"插入{self.table_name}成功")
                return True
        except Exception as e:
            logger.error(f"插入{self.table_name}失败: {e}")
            raise
    
    def _update(self) -> bool:
        """
        更新现有记录
        """
        try:
            with get_db_connection() as conn:
                # 获取所有字段和值
                data = self.to_dict()
                
                # 确保主键存在
                if self.primary_key not in data:
                    raise ValueError(f"主键字段{self.primary_key}不存在")
                
                # 移除主键（从更新字段中）
                id_value = data.pop(self.primary_key)
                
                # 构建更新语句
                set_clause = ', '.join([f"{key} = %s" for key in data.keys()])
                query = f"UPDATE {self.table_name} SET {set_clause} WHERE {self.primary_key} = %s"
                
                # 执行更新
                params = list(data.values()) + [id_value]
                execute_query(conn, query, params)
                conn.commit()
                
                logger.info(f"更新{self.table_name}成功")
                return True
        except Exception as e:
            logger.error(f"更新{self.table_name}失败: {e}")
            raise
    
    def delete(self) -> bool:
        """
        删除记录
        """
        try:
            with get_db_connection() as conn:
                # 确保主键存在
                if not hasattr(self, self.primary_key):
                    raise ValueError(f"主键字段{self.primary_key}不存在")
                
                id_value = getattr(self, self.primary_key)
                if id_value is None:
                    raise ValueError("主键值为空")
                
                # 执行删除
                query = f"DELETE FROM {self.table_name} WHERE {self.primary_key} = %s"
                execute_query(conn, query, (id_value,))
                conn.commit()
                
                logger.info(f"删除{self.table_name}成功")
                return True
        except Exception as e:
            logger.error(f"删除{self.table_name}失败: {e}")
            raise